package course.dao.impl;

import course.dao.BaseDao;
import course.dao.CollegeDao;
import course.pojo.entity.College;
import course.pojo.vo.Page;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.stream.Collectors;

/**
 * @author HLF
 * @date 2022/6/30 20:17
 */
public class CollegeDaoImpl extends BaseDao<College> implements CollegeDao {
    @Override
    public College getById(Integer cid, Connection connection) {
        String sql = "select cid, cname, caddress, cphone, " +
                "create_time createTime, create_by createBy, update_time updateTime, update_by updateBy " +
                "from t_college where cid = ?";
        return getOneBean(connection, sql, cid);
    }

    @Override
    public int addCollege(College college, Connection connection) {
        String sql = "insert into t_college values(?,?,?,?,?,?,?,?);";
        return insert(connection, sql, college.getCid(), college.getCname(),
                college.getCaddress(), college.getCphone(),
                college.getCreateTime(), college.getCreateBy(),
                college.getUpdateTime(), college.getUpdateBy()
        );
    }

    @Override
    public int updateById(College college, Connection connection) {
        String sql = "update t_college set cname = ?,caddress = ?,cphone = ?,update_time = ?,update_by = ? where cid = ?";
        return update(connection, sql, college.getCname(), college.getCaddress(),
                college.getCphone(), college.getUpdateTime(), college.getUpdateBy(),
                college.getCid());
    }

    @Override
    public int deleteById(Integer cid, Connection connection) {
        String sql = "delete from t_college where cid = ?;";
        return update(connection, sql, cid);
    }

    @Override
    public List<College> getList(Connection connection) {
        String sql = "select cid, cname, caddress, cphone, " +
                "create_time createTime, create_by createBy, update_time updateTime, update_by updateBy " +
                "from t_college";
        return getBeanList(connection, sql);
    }

    @Override
    public int deleteSelect(List<Integer> ids, Connection connection) {
        String sql = "delete from t_college where cid in(?)";

        String sqlIN = ids.stream()
                .map(x -> String.valueOf(x))
                .collect(Collectors.joining(",", "(", ")"));

        sql = sql.replace("(?)", sqlIN);

        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);

            return preparedStatement.executeUpdate();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public long count(Connection connection) {
        String sql = "select count(*) from t_college;";
        return (long) getCount(connection, sql);
    }

    /**
     * 分页
     *
     * @param pageNo 当前页码数
     * @return 返回通用的分页对象
     */
    @Override
    public Page<College> page(Integer pageNo, Connection connection) {
        Page<College> page = new Page<>();
        Integer pageSize = page.getPageSize();

        String sql = "select cid, cname, caddress, cphone, " +
                "create_time createTime, create_by createBy, update_time updateTime, update_by updateBy " +
                "from t_college limit ?,?;";
        List<College> list = getBeanList(connection, sql, (pageNo - 1) * pageSize, pageSize);

        page.setPageNo(pageNo);
        page.setPageSize(pageSize);
        page.setList(list);
        long total = this.count(connection);
        page.setTotal(total);

        int pages = (int) Math.ceil((double) total / pageSize);
        page.setPages(pages);

        return page;
    }

    /**
     * 按照学院关键字查找，并返回分页对象
     */
    @Override
    public Page<College> query(String cname, Integer pageNo, Connection connection) {
        Page<College> page = new Page<>();
        Integer pageSize = page.getPageSize();

        String sql = "select cid, cname, caddress, cphone, " +
                "create_time createTime, create_by createBy, update_time updateTime, update_by updateBy " +
                "from t_college where cname like ? limit ?,?;";
        String key = "%" + cname + "%";
        List<College> list = getBeanList(connection, sql, key, (pageNo - 1) * pageSize, pageSize);

        String sql2 = "select count(*) from t_college where cname like ?;";
        long count = (long) getCount(connection, sql2, key);

        page.setPageNo(pageNo);
        page.setPageSize(pageSize);
        page.setList(list);
        page.setTotal(count);

        int pages = (int) Math.ceil((double) count / pageSize);
        page.setPages(pages);

        return page;
    }

    @Override
    public College getByCname(String cname, Connection connection) {
        String sql = "select cid, cname, caddress, cphone, " +
                "create_time createTime, create_by createBy, update_time updateTime, update_by updateBy " +
                "from t_college where cname =?;";
        return getOneBean(connection, sql, cname);
    }

    @Override
    public List<College> selectBathByIds(List<Integer> ids, Connection connection) {
        String sql = "select cid, cname, caddress, cphone, " +
                "create_time createTime, create_by createBy, update_time updateTime, update_by updateBy " +
                "from t_college where cid in(?);";

        String sqlIN = ids.stream()
                .map(x -> String.valueOf(x))
                .collect(Collectors.joining(",", "(", ")"));

        sql = sql.replace("(?)", sqlIN);

        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            ResultSet rs = preparedStatement.executeQuery();
            List<College> colleges = new ArrayList<>();
            while (rs.next()) {
                College college = new College();
                college.setCid(rs.getInt(1));
                college.setCname(rs.getString(2));
                college.setCaddress(rs.getString(3));
                college.setCphone(rs.getString(4));

                colleges.add(college);
            }
            return colleges;

        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
}
